

ALTER   function fn_select_previous_locations (@lot_id bigint, @lot_number varchar(20))
returns @fn_results Table(Lot_ID bigint, Location_Name varchar(50))
AS
begin
	declare @results Table(Lot_ID bigint, Location_Name varchar(50))	
	declare @temp_process_step_id bigint
	declare @temp_location_id int
	declare @temp_lot_id bigint
	declare @temp_location_name varchar(50)
	declare @temp_ps_type varchar(14)
	declare @temp_ps_type_name varchar(20)
	declare @temp_cursor CURSOR

	set @temp_cursor = CURSOR FOR
	select Lot_ID, ProcessStep_ID, Location_ID from Lot_Table where Lot_Number = @lot_number and Lot_ID <= @lot_id order by Lot_ID DESC

	open @temp_cursor
		fetch next from @temp_cursor into @temp_lot_id, @temp_process_step_id, @temp_location_id
		while @@fetch_status = 0 
		begin
			select @temp_ps_type = PS_Type,  @temp_ps_type_name = PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
			if @temp_ps_type_name = 'FAB'
			begin
				set @temp_ps_type = @temp_ps_type_name
			end
			select @temp_location_name = @temp_ps_type+' - '+l.L_Vendor_Name+'/'+a.City from Location l, Address a where Location_ID =@temp_location_id and l.Address_ID = a.Address_ID
			insert @results(Lot_ID, Location_Name) values(@temp_lot_id, @temp_location_name)

			fetch next from @temp_cursor into @temp_lot_id, @temp_process_step_id, @temp_location_id
		end
	close @temp_cursor
	deallocate @temp_cursor
	
	insert @fn_results select *from @results
	return
end
